

/*
	Base Business Layer.
	(C) Lehavi Solutions	

	Legend:
	
	2009-01-14 (Dima Michaelov): Columns enum has been restructured. Now more than 63 columns can be defined per table
*/
	
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Linq;


namespace BLL
{	
	public partial class Users
	{
		#region Columns
		public enum  Columns : long
		{
            ID = 0, 
            GoogleID = 1, 
            Token = 2, 
            ClarizenSession = 3, 
            UserName = 4, 
            ClarizenUserID = 5, 
            GoogleOauthToken = 6, 
            GoogleOauthSecret = 7			
		}
		#endregion


		#region Basic CRUD
		
		// Retrieve
		public static BO.User GetByID(object ID)
		{
			return Get().Where(Columns.ID, Actions.Equal, ID).SelectOne();
		}
		
		// Create / Update / Delete
		public static int Insert(BO.User user)
		{
			int ID = DAL.Users.Insert(user);
			user.ID = ID;
			return ID;
		}
		
		public static void Update(BO.User user)
		{
			DAL.Users.Update(user);
		}
		
		public static void Delete(object ID)
		{
			DAL.Users.Delete(ID);
		}
		
		public static void SafeDelete(object ID)
		{
			var column = (Columns)EntityBaseUtils.GetEntityStatusColumn(typeof (Users));
			DAL.Users.SetValue(ID, column.ToString(), EntityStatuses.Deleted);
		}
		
		public static void SafeDeleteCascade(object ID)
		{
			SafeDelete(ID);
			EntityBaseUtils.SafeDeleteCascade(typeof(Users), ID);
		}
		
		public static void SafeDetach(Columns foreignKey, object fkID)
		{
			DAL.Users.SafeDetach(fkID, foreignKey.ToString());
		}
	
		#endregion
		
		#region Fluent Select
		public static SqlQuery Get()
		{
			return new SqlQuery();
		}

		
		public class SqlQuery : SqlQueryBase
		{
			Columns[] columns;
			List<Parameter> parameters;
			Columns? orderByColumns;

			public override string TableName { get { return "Users"; } }
			
			#region WhereParameters		
			public class Parameter
			{
				internal Columns column;
				internal Actions action;
				internal object value;
	
				public Parameter(Columns c, Actions a, object v)
				{
					column = c;
					action = a;
					value = v;
				}
	
				public Parameter(Columns c, Actions a)
				{
					column = c;
					action = a;
					value = null;
				}
			}
			#endregion

			internal SqlQuery()
			{
				parameters = new List<Parameter>();
				Joins = new List<SqlJoin>();
			}

			// Fluent Select
			public SqlQuery Where(Columns column, Actions action, object value)
			{
				parameters.Add(new Parameter(column, action, value));

				return this;
			}
			
			public SqlQuery OrderBy(Columns orderBy)
			{
				return OrderBy(orderBy, false);
			}
			
			public SqlQuery OrderBy(Columns orderBy, bool Descending)
			{
				orderByColumns = orderBy;
				orderDescending = Descending;

				return this;
			}
			
			public SqlQuery Join(SqlQueryBase query, object ForeignKey)
			{
				var join = new SqlJoin { JoinQuery = query, ForeignKey = ForeignKey, Parent = this};
				Joins.Add(join);
				return this;
			}
			
			public SqlQuery Join(SqlQueryBase query, object LocalKey, object OtherKey)
			{
				var join = new SqlJoin { JoinQuery = query, ForeignKey = OtherKey, LocalKey = LocalKey, Parent = this };
				Joins.Add(join);
				return this;
			}
			
			public SqlQuery Limit(int IndexFrom, int IndexTo)
			{
				isPaging = true;
				indexFrom = IndexFrom;
				indexTo = IndexTo;

				return this;
			}

			// End of fluent select
			public List<BO.User> Select()
			{
				return Select(GetAllColumns());
			}
			
			public List<BO.User> Select(params Columns[] columns)
			{
				if (columns == null || columns.Length == 0)
					throw new ArgumentException("Must specify at least one column");

				// If All columns specified as first argument then we populate array with all enums except All
				this.columns = columns;
				
				var parameters = PrepareParameters();
				
				var list = new List<BO.User>();
				var objectList = ConvertTableToEntity(
					DAL.Users.Select(ToString(false), parameters.ToArray()),
					this);

				foreach (var o in objectList)
				{
					list.Add((BO.User)o);
				}

				return list;	
			}
			
			public BO.User SelectOne()
			{
				var list = Select();
				if (list.Count == 0)
					return null;
				return list[0];
			}
			
			public BO.User SelectOne(params Columns[] columns)
			{
				var list = Select(columns);
				if (list.Count == 0)
					return null;
				return list[0];
			}
			
			public int Count()
			{
				var parameters = PrepareParameters();
				return Convert.ToInt32(DAL.Users.Count(ToString(true), parameters.ToArray()));
			}

			// helper methods
			private Columns[] GetAllColumns()
			{
				var enumColumns = Enum.GetValues(typeof(Columns));
				var fieldColumns = new Columns[enumColumns.Length];
				enumColumns.CopyTo(fieldColumns, 0);
				return fieldColumns;
			}
			
			protected override void GetFieldList(StringBuilder cmd, string prefix)
			{
				if (columns == null) columns = GetAllColumns();

				foreach (var column in columns)
				{
					cmd.AppendFormat("{0}.{1} as {0}_{1}, ", prefix, column);
				}
			}
			
			protected override void GetParameterList(StringBuilder cmd, int tableIndex, ref int parameterIndex)
			{
				if (parameters != null)
				{
					for (int i = 0; i < parameters.Count; i++)
					{
						#region Fields
						cmd.Append("t" + tableIndex + "." +  parameters[i].column);
						#endregion
		
						#region Action
		
						switch (parameters[i].action)
						{
							case Actions.Equal:
								// checking for "= null", and converting to IS NULL
								if (parameters[i].value != null)	cmd.Append(" = ");
								else							cmd.Append(" is null ");
								break;
							case Actions.NotEqual:
								// checking for "<> null", and converting to IS NOT NULL
								if (parameters[i].value != null)	cmd.Append(" <> ");
									else							cmd.Append(" is not null ");
								break;
							case Actions.BiggerThan:
								if (parameters[i].value == null) throw new ArgumentException("Null parameter was supplied for non-nullable SQL action");
								cmd.Append(" > ");
								break;
							case Actions.BiggerThanOrEqual:
								if (parameters[i].value == null) throw new ArgumentException("Null parameter was supplied for non-nullable SQL action");
								cmd.Append(" >= ");
								break;
							case Actions.SmallerThan:
								if (parameters[i].value == null) throw new ArgumentException("Null parameter was supplied for non-nullable SQL action");
								cmd.Append(" < ");
								break;
							case Actions.SmallerThanOrEqual:
								if (parameters[i].value == null) throw new ArgumentException("Null parameter was supplied for non-nullable SQL action");
								cmd.Append(" <= ");
								break;
							case Actions.Like:
								if (parameters[i].value == null) throw new ArgumentException("Null parameter was supplied for non-nullable SQL action");
								cmd.Append(" like ");
								break;
							case Actions.NotLike:
								if (parameters[i].value == null) throw new ArgumentException("Null parameter was supplied for non-nullable SQL action");
								cmd.Append(" not like ");
								break;
							case Actions.In:
								if (parameters[i].value == null) throw new ArgumentException("Null parameter was supplied for non-nullable SQL action");
								cmd.Append(" in ");
								break;
							case Actions.NotIn:
								if (parameters[i].value == null) throw new ArgumentException("Null parameter was supplied for non-nullable SQL action");
								cmd.Append(" not in ");
								break;
								
						}
						#endregion
						
						#region Parameters
		
						if (parameters[i].value != null)
						{
							if (parameters[i].action == Actions.In || parameters[i].action == Actions.NotIn)
							{
								var inValues = parameters[i].value as object[];
								if (inValues == null || inValues.Length == 0) throw new ArgumentException("SQL IN operator's data must be array of Object.");
	
								if (inValues[0] is string)
								{
									cmd.Append("('");
									cmd.Append(string.Join("', '", inValues.Cast<string>().ToArray()));
									cmd.Append("') ");
								}
								else
								{
									cmd.Append("(");
									cmd.Append(string.Join(", ", inValues.Cast<string>().ToArray()));
									cmd.Append(") ");
								}
							}
							else
							{
								cmd.Append("@p" + parameterIndex);
								cmd.Append(" ");
			
							}
						}
						
						#endregion
		
						parameterIndex++;
		
						cmd.Append("and ");
					}
				}
			}
			
			protected override IEnumerable<SqlParameter> GetParameters(ref int parameterIndex)
			{
				var parameters = new List<SqlParameter>();

				foreach (var p in this.parameters)
				{
					if (p.value == null || p.value is object[])
					{
						parameterIndex++;
						continue;
					}
					
					parameters.Add(new SqlParameter 
					{ 
						ParameterName = "p" + parameterIndex,
						Value = p.value
					});

					parameterIndex++;

				}
				return parameters;
			}
			
			protected override string GetOrderBy()
			{
				return orderByColumns.HasValue ? String.Format("{0} {1}",
					orderByColumns, orderDescending ? "DESC " : String.Empty) : String.Empty;
			}
	
		}

		
		#endregion


	}
	
}



